package com.wanxi.dao.Impl;

import com.wanxi.dao.NewsDao;
import com.wanxi.model.NewsModel;
import com.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Li
 * @Date 2023/5/7 12:12
 */
public class NewDaoImpl implements NewsDao {
    public int add(NewsModel newsModel) {
        String sql = "insert into news (`title`,`img`, `time`,`content`,`enable`,`remark`,`update_time`,`news_id`) " +
                "value('" + newsModel.getTitle() + "','" + newsModel.getImg() + "',now()," +
                "'" + newsModel.getContent() + "','" + newsModel.getEnable() + "','" + newsModel.getRemark() + "',sysdate(),'"+newsModel.getNewsId()+"');";
        return JDBC.update(sql);
    }

    public int update(NewsModel newsModel) {
        String sql = "update news set `title` = '" + newsModel.getTitle() + "', `img` = '" + newsModel.getImg() + "'," +
                " `content` = '" + newsModel.getContent() + "', `enable` = '" + newsModel.getEnable() + "'," +
                " `remark` = '" + newsModel.getRemark() + "',`update_time` = now(),`news_id` = '"+newsModel.getNewsId()+"' where (`id`='" + newsModel.getId() + "') ";
        return JDBC.update(sql);
    }

    public int delete(NewsModel newsModel) {
        String sql = "delete from news where id='" + newsModel.getId() + "' ";
        return JDBC.update(sql);
    }

    public List<NewsModel> findAll(NewsModel newsModel) {
        int start = (newsModel.getPage()-1)*newsModel.getLimit();
        String sql = "SELECT * FROM (SELECT n.*,t.new_type newsName FROM news n LEFT JOIN news_type t ON n.news_id=t.id) temp WHERE TRUE";
//        String sql = "select *,date_format(time ,'%Y-%m-%d') as time from news where true";
         sql += getWhereSql(newsModel);
        sql += " order by id desc";
        sql += " limit "+start+","+newsModel.getLimit()+"";
        ResultSet query = JDBC.query(sql);
        List<NewsModel> newsModelList = new ArrayList<NewsModel>();
        try {
            while (query.next()) {
                newsModelList.add(NewsModel.builder()
                        .id(query.getInt("id"))
                        .title(query.getString("title"))
                        .img(query.getString("img"))
                        .time(query.getString("time"))
                        .content(query.getString("content"))
                        .enable(query.getInt("enable"))
                        .remark(query.getString("remark"))
                        .newsId(query.getInt("news_id"))
                        .newsName(query.getString("newsName"))
                        .build());
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return newsModelList;
    }

    private String getWhereSql(NewsModel newsModel) {
        String sql = "";
        if (!"".equals(newsModel.getTitle())) {
            sql += " and title like '%" + newsModel.getTitle() + "%' ";
        }
        if (!"".equals(newsModel.getContent())) {
            sql += " and content like '%" + newsModel.getContent() + "%' ";
        }
        if (newsModel.getEnable()!=-1) {
            sql += " and enable = '" + newsModel.getEnable() + "' ";
        }
        if (!"".equals(newsModel.getStartTime())) {
            sql += " and time >='" + newsModel.getStartTime() + "'";
        }
        if (!"".equals(newsModel.getEndTime())) {
            sql += " and time <='" + newsModel.getEndTime() + "'";
        }

        return sql;
    }

    /**
     * 根据id查询
     *
     * @param newsModel
     * @return
     */
    public NewsModel findById(NewsModel newsModel) {
        String sql = "select * from news where id = '" + newsModel.getId() + "'";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return NewsModel.builder()
                        .id(query.getInt("id"))
                        .title(query.getString("title"))
                        .img(query.getString("img"))
                        .content(query.getString("content"))
                        .time(query.getString("time"))
                        .enable(query.getInt("enable"))
                        .newsId(query.getInt("news_id"))
                        .remark(query.getString("remark"))
                        .build();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return null;
    }

    public Integer count(NewsModel newsModel) {
        String sql = "select count(*) as count from `news` where true";
       sql += getWhereSql(newsModel);
       return JDBC.count(sql);
    }

    /**
     * 查询总数
     * @return
     */
    @Override
    public Integer totalCount() {
        String sql = "select count(*) as count from `news`";
        return JDBC.count(sql);
    }

    public boolean findSameName(NewsModel newsModel) {
        String sql = "select * from news where title = '" + newsModel.getTitle() + "' ";
        ResultSet query = JDBC.query(sql);
        try {
            while (query.next()) {
                return true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return false;
    }
}
